﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;

namespace chessLogic
{
    public class DataBase
    {
        private DBDriver driver;
        private Fen fen;

        public DataBase()
        {
            driver = new DBDriver();
            fen = new Fen();
        }

        public void InsertGames(List<Game> games)
        {
            driver.OpenConnect();
            DbConnection connection = driver.connect;
            using (DbTransaction transaction = connection.BeginTransaction())
            {
                DbCommand gameCommand = GetCommandGame(transaction, connection);
                DbCommand moveCommand = GetCommandMove(transaction, connection);
                foreach (Game game in games)
                {
                    try
                    {
                        InsertGame(gameCommand, game);
                        InsertMove(moveCommand, game);
                        driver.command.Dispose();
                    }
                    catch (Exception ex)
                    {
                        new Exception(ex.Message);
                    }
                }
                gameCommand.Dispose();
                moveCommand.Dispose();
                transaction.Commit();
            }
            driver.CloseConnect();
        }
        private DbCommand GetCommandGame(DbTransaction transaction, DbConnection connection)
        {
            DbCommand command = connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText = "INSERT INTO games (event,site,date,round,white,black,result,eco)" +
                                    " VALUES (?,?,?,?,?,?,?,?)";
            DbParameter _event = command.CreateParameter();
            DbParameter site = command.CreateParameter();
            DbParameter date = command.CreateParameter();
            DbParameter round = command.CreateParameter();
            DbParameter white = command.CreateParameter();
            DbParameter black = command.CreateParameter();
            DbParameter result = command.CreateParameter();
            DbParameter ECO = command.CreateParameter();
            command.Parameters.Add(_event);
            command.Parameters.Add(site);
            command.Parameters.Add(date);
            command.Parameters.Add(round);
            command.Parameters.Add(white);
            command.Parameters.Add(black);
            command.Parameters.Add(result);
            command.Parameters.Add(ECO);
            return command;
        }
        private DbCommand GetCommandMove(DbTransaction transaction, DbConnection connection)
        {
            DbCommand command = connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText = "INSERT INTO moves (game_id,move_num,movestring,fen)" +
                                      " VALUES (?,?,?,?)";
            DbParameter gameId = command.CreateParameter();
            DbParameter moveNum = command.CreateParameter();
            DbParameter moveString = command.CreateParameter();
            DbParameter fen = command.CreateParameter();
            command.Parameters.Add(gameId);
            command.Parameters.Add(moveNum);
            command.Parameters.Add(moveString);
            command.Parameters.Add(fen);
            return command;
        }
        private void InsertGame(DbCommand command, Game game)
        {
            command.Parameters[0].Value = game.Event;
            command.Parameters[1].Value = game.Site;
            command.Parameters[2].Value = game.Date;
            command.Parameters[3].Value = game.Round;
            command.Parameters[4].Value = game.White;
            command.Parameters[5].Value = game.Black;
            command.Parameters[6].Value = game.Result;
            command.Parameters[7].Value = game.ECO;
            command.ExecuteNonQuery();
        }
        private void InsertMove(DbCommand command, Game game)
        {
            DataTable queryResult = driver.GetData("SELECT MAX(game_id) FROM games");
            int gameID = Int32.Parse(queryResult.Rows[0][0].ToString());
            Chess chess = new Chess();
            chess.currentGame = game;
            chess.InitialChess();
            for (int i = 0; i < game.Moves.Count; i++)
            {
                command.Parameters[0].Value = gameID;
                command.Parameters[1].Value = i;
                command.Parameters[2].Value = game.Moves[i];
                chess.MakeNextMove();
                fen.CreateFen(chess.board, chess.currentGame.getWhoMove());
                command.Parameters[3].Value = fen.FenString;
                command.ExecuteNonQuery();
            }
        }
        public List<string> GetMovesFromDataBaseByGameId(int gameId)
        {
            List<string> moves = new List<string>();
            DataTable queryResult = driver.GetData("SELECT * FROM moves WHERE game_id='" + gameId + "'");
            for (int j = 0; j < queryResult.Rows.Count; j++)
                moves.Add(queryResult.Rows[j][3].ToString());
            queryResult.Clear();
            queryResult.Reset();
            return moves;
        }
        public string[,] GetStatistics(string fen)
        {
            DataTable queryGameResult = driver.GetData("SELECT games.game_id, games.result, mv.movestring FROM games,(SELECT game_id,movestring FROM moves WHERE fen='" + fen + "')as mv WHERE games.game_id IN"
                                                      + " (SELECT     game_id"
                                                       + " FROM moves "
                                                       + " WHERE fen = '" + fen + "') AND games.game_id = mv.game_id");
            string[,] queryTable = new string[queryGameResult.Rows.Count, 3];
            for (int j = 0; j < queryGameResult.Rows.Count; j++)
            {
                for (int i = 0; i < 3; i++)
                {
                    queryTable[j, i] = queryGameResult.Rows[j][i].ToString();
                }
            }
            return queryTable;
        }
    }
}